// StudentRepository.java
package com.example.orderfood2.Repository;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.orderfood2.entity.Student;

import java.util.ArrayList;
import java.util.List;

public class StudentRepository extends BaseRepository {

    public StudentRepository(Context context) {
        super(context);
    }

//    public long registerStudent(Student student) {
//        open();
//        ContentValues values = createContentValues();
//        values.put("username", student.getUsername());
//        values.put("password", student.getPassword());
//        values.put("sex", student.getSex());
//        values.put("classId", student.getClassId());
//        values.put("score", student.getScore());
//        values.put("date", student.getDate());
//        values.put("enrol", student.getEnrol());
//        long result = database.insert("student", null, values);
//        close();
//        return result;
//    }

    public boolean loginStudent(String username, String password) {
        open();
        boolean result = false;
        Cursor cursor = database.query("student", null, "username=?",
                new String[]{username}, null, null, null);

        if (cursor != null) {
            try {
                if (cursor.moveToFirst()) {
                    String dbPassword = cursor.getString(cursor.getColumnIndexOrThrow("password"));
                    result = dbPassword.equals(password);
                }
            } finally {
                cursor.close();
            }
        }
        close();
        return result;
    }

    //    student表id,username的查
    public Cursor getAllStudents() {
        open();
        Cursor cursor = database.rawQuery("SELECT id, username FROM student", null);
//        close();
        return cursor;

    }

    //    student修改密码
    public  int StudentChangePassword(String username,String password){
        open();
        ContentValues S=new ContentValues();
        S.put("password",password);
        int Student=database.update("student",S,"username=?",new String[]{username});
        close();
        return Student;
    }

    //通过id查询student
    public Student getStudentById(int id) {
        open();
        Cursor cursor = database.query("student",
                null,
                "id=?",
                new String[]{String.valueOf(id)},
                null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            Student student = new Student(
                    cursor.getInt(0),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4),
                    cursor.getString(5),
                    cursor.getString(6),
                    cursor.getString(7)
            );
            cursor.close();
            close();
            return student;
        }
        close();
        return null;
    }

    //student表中的生日日期
    public String getStudentDate(String account){
        open();
        Cursor cursor = null;
        String date = null;
        try {
            cursor = database.query("student", null, "username=?", new String[]{account}, null, null, null);
            if (cursor != null && cursor.moveToFirst()) {
                date = cursor.getString(6); // date 是第6列
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        close();
        return date;
    }
    // student表的更新
    public boolean updateStudent(int id, Student student) {
        open();
        ContentValues values = new ContentValues();
        values.put("username", student.getUsername());
        values.put("password", student.getPassword());
        values.put("sex", student.getSex());
        values.put("classId", student.getClassId());
        values.put("score", student.getScore());
        values.put("date", student.getDate());
        values.put("enrol", student.getEnrol());

        boolean student1 = database.update("student", values, "id=?", new String[]{String.valueOf(id)}) > 0;
        close();
        return student1;
    }

    //    student成绩排行
    public List<Student> getStudentsByScienceOrderByScoreDesc(String science){
        List<Student> studentList=new ArrayList<>();
        open();
        // 1. 查询 class_info 表，获取 classname = science 的所有 classnumber
        Cursor classCursor = database.rawQuery(
                "SELECT classnumber FROM class_info WHERE classname = ?",
                new String[]{science}
        );
        if(classCursor!=null){
            try{
                while (classCursor.moveToNext()){
                    String classnumber=classCursor.getString(0);
                    // 2. 查询 student 表，获取 classId = classnumber 的所有学生，并按 score 降序排序
                    Cursor studentCursor=database.rawQuery("SELECT * FROM student WHERE classId = ? ORDER BY score DESC",new String[]{classnumber});
                    if(studentCursor!=null){
                        try {
                            while (studentCursor. moveToNext()){
                                Student student = new Student(
                                        studentCursor.getInt(0),    // id
                                        studentCursor.getString(1), // username
                                        studentCursor.getString(2), // password
                                        studentCursor.getString(3), // sex
                                        studentCursor.getString(4), // classId
                                        studentCursor.getString(5), // score
                                        studentCursor.getString(6), // date
                                        studentCursor.getString(7)  // enrol
                                );
                                studentList.add(student);
                            }
                        }finally {
                            studentCursor.close();
                        }
                    }
                }
            }finally {
                classCursor.close();
            }
        }
        close();
        return studentList;
    }
    //    student表的添加
    public long registerStudent(Student student){
        open();
        ContentValues S=new ContentValues();
        S.put("username",student.getUsername());
        S.put("password",student.getPassword());
        S.put("sex",student.getSex());
        S.put("classId",student.getClassId());
        S.put("score",student.getScore());
        S.put("date",student.getDate());
        S.put("enrol",student.getEnrol());
        long student1 = database.insert("student", null, S);
        close();
        return student1;
    }

    //    student表的删除
    public boolean deleteStudent(int id){
        open();
        boolean student = database.delete("student", "id=?", new String[]{String.valueOf(id)}) > 0;
        close();
        return student;
    }
//    根据学生名模糊搜索
    public List<Student> getStudentsByName(String name){
        List<Student> studentList=new ArrayList<>();
        open();
        Cursor cursor = database.rawQuery("SELECT * FROM student WHERE username LIKE ?", new String[]{"%" + name + "%"});
        if(cursor!=null){
            try{
                while (cursor.moveToNext()){
                    Student student = new Student(
                            cursor.getInt(0),    // id
                            cursor.getString(1), // username
                            cursor.getString(2), // password
                            cursor.getString(3), // sex
                            cursor.getString(4), // classId
                            cursor.getString(5), // score
                            cursor.getString(6), // date
                            cursor.getString(7)// enrol
                    );
                    studentList.add(student);
                }

            }
            finally {
                cursor.close();
            }
        }
        close();
        return studentList;
    }
//    根据学生名返回id
    public int getStudentIdByName(String name){
        open();
        int id = -1;
        Cursor cursor = database.rawQuery("SELECT id FROM student WHERE username = ?", new String[]{name});
        if(cursor!=null){
            try{
                if(cursor.moveToFirst()){
                    id = cursor.getInt(0);
                }
            }
            finally {
                cursor.close();
            }
        }
        close();
        return id;
    }
}